package mediatriggerservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.knopflerfish.service.log.LogRef;
import org.osgi.service.event.EventAdmin;

public class MediaPlayTrigger 
{
	private static final String MEDIA_NORMAL = "NM";
	private static final String DEPARTURE ="D";
	private  LogRef log;
	private MediaTriggerServiceConfig config;
	private DBConnectionPool dbConnectionPool ;
	private EventAdmin eventAdmin;
	private String triggerDescriptorsQuery = "SELECT b.id, loop_till_next_trigger_flag, media_type, media_code, media_duration, seq_no FROM trigger_desc_media_components a, trigger_descriptors b WHERE a.trigger_desc_id = b.id  AND b.node_code= ? AND b.route_schedule_code=? AND b.trigger_condition=? ORDER by a.seq_no";	
	private String mediaDescriptorsQuery = "SELECT absolute_media_path, media_duration, relative_media_path FROM media_descriptors WHERE media_code=? AND percentage_downloaded=100";
	private String mediaPlayerQueueInsert = "INSERT INTO media_player_queue VALUES(default, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private String persistentDataUpdateTriggerId = "UPDATE persistent_data SET current_trigger_descriptor_id=?";
	private String successfullyDownloadedMediaQuery = "SELECT relative_media_path, absolute_media_path, media_duration FROM previously_successfully_downloaded_medias WHERE media_code=?";
	
	public MediaPlayTrigger(EventAdmin eventAdmin)
	{
		try
		{
			log = new LogRef(Activator.bc);
			config = MediaTriggerServiceConfig.getConfig();
			dbConnectionPool = DBConnectionPool.getInstance(config.getJdbcConnectionURI());
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	protected synchronized void executeMediaTrigger(String currentRouteCode, String newNodeCode, String lastNodeCode, String triggerCondition)
	{
		int triggerDescriptorId = 0;
		Connection conn = null;
		PreparedStatement preparedStmt1 = null;
		PreparedStatement preparedStmt2 = null;
		PreparedStatement preparedStmt2A = null;
		PreparedStatement preparedStmt3 = null;
		PreparedStatement preparedStmt4 = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs2A = null;
		try 
		{
			conn = dbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			
			preparedStmt1 = dbConnectionPool.getPreparedStmt(conn, triggerDescriptorsQuery);
			preparedStmt1.setString(1, newNodeCode);
			preparedStmt1.setString(2, currentRouteCode);
			preparedStmt1.setString(3, triggerCondition);
			rs1 = preparedStmt1.executeQuery();
			while (rs1.next() == true)
			{
				triggerDescriptorId = rs1.getInt(1);
				String loopTillNextTriggerFlag = rs1.getString(2);
				String mediaType = rs1.getString(3);
				String mediaCode = rs1.getString(4);
				int mediaDuration = rs1.getInt(5);
				int mediaSeqNo = rs1.getInt(6);
				if (mediaType.equals(MEDIA_NORMAL))
				{
					String absoluteMediaPath = null;
					String relativeMediaPath = "";
					int normalMediaDuration =0;
					preparedStmt2 = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsQuery);
					preparedStmt2.setString(1, mediaCode);
					rs2 = preparedStmt2.executeQuery();
					if (rs2.next() == true)
					{
						absoluteMediaPath = rs2.getString(1);
						normalMediaDuration = rs2.getInt(2);
						relativeMediaPath = rs2.getString(3);
						//System.out.println("absoluteMediaPath: "+absoluteMediaPath);
					}
					rs2.close();
					rs2 = null;
					preparedStmt2.close();
					preparedStmt2 = null;
					if (absoluteMediaPath == null || absoluteMediaPath.trim().equals(""))
					{
						preparedStmt2A = dbConnectionPool.getPreparedStmt(conn, successfullyDownloadedMediaQuery);
						preparedStmt2A.setString(1, mediaCode);
						rs2A = preparedStmt2A.executeQuery();
						if (rs2A.next() == true)
						{
							relativeMediaPath = rs2A.getString(1);
							absoluteMediaPath = rs2A.getString(2);
							normalMediaDuration = rs2A.getInt(3);
						}
						rs2A.close();
						rs2A = null;
						preparedStmt2A.close();
						preparedStmt2A = null;
						if (absoluteMediaPath == null || absoluteMediaPath.trim().equals(""))
						{
							absoluteMediaPath = "Bad Path";
						}
					}
					preparedStmt3 = dbConnectionPool.getPreparedStmt(conn, mediaPlayerQueueInsert);
					preparedStmt3.setString(1, mediaType);
					preparedStmt3.setString(2, absoluteMediaPath);
					preparedStmt3.setInt(3, normalMediaDuration);
					preparedStmt3.setString(4, mediaCode);
					preparedStmt3.setString(5, loopTillNextTriggerFlag);
					preparedStmt3.setInt(6, mediaSeqNo);
					preparedStmt3.setString(7, triggerCondition);
					if (triggerCondition.trim().equals(DEPARTURE))
						preparedStmt3.setString(8, lastNodeCode);
					else
						preparedStmt3.setString(8, newNodeCode);
					preparedStmt3.setString(9, relativeMediaPath);
					preparedStmt3.executeUpdate();
					preparedStmt3.close();
					preparedStmt3 = null;
					System.out.println("Inserted "+absoluteMediaPath + " into media player queue");
				}
				else
				{
					preparedStmt3 = dbConnectionPool.getPreparedStmt(conn, mediaPlayerQueueInsert);
					preparedStmt3.setString(1, mediaType);
					preparedStmt3.setString(2, "");
					preparedStmt3.setInt(3, mediaDuration);
					preparedStmt3.setString(4, "");
					preparedStmt3.setString(5, loopTillNextTriggerFlag);
					preparedStmt3.setInt(6, mediaSeqNo);
					preparedStmt3.setString(7, triggerCondition);
					if (triggerCondition.trim().equals(DEPARTURE))
						preparedStmt3.setString(8, lastNodeCode);
					else
						preparedStmt3.setString(8, newNodeCode);
					preparedStmt3.setString(9, "");
					preparedStmt3.executeUpdate();
					preparedStmt3.close();
					preparedStmt3 = null;
					System.out.println("Inserted NOT NORMAL media into media player queue");
				}
			}
			rs1.close();
			rs1 = null;
			preparedStmt1.close();
			preparedStmt1 = null;
			
			preparedStmt4 = dbConnectionPool.getPreparedStmt(conn, persistentDataUpdateTriggerId);
			preparedStmt4.setInt(1, triggerDescriptorId);
			preparedStmt4.executeUpdate();
			preparedStmt4.close();
			preparedStmt4 = null;
			conn.commit();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
    		if (rs1 != null)
    		{
    			try {rs1.close();} catch (SQLException e) {} 
    			rs1 = null;
    		}
       		if (rs2 != null)
    		{
    			try {rs2.close();} catch (SQLException e) {} 
    			rs2 = null;
    		}
       		if (rs2A != null)
    		{
    			try {rs2A.close();} catch (SQLException e) {} 
    			rs2A = null;
    		}
    		if (preparedStmt1 != null)
    		{
    			try {preparedStmt1.close();} catch (SQLException e) {} 
    			preparedStmt1 = null;
    		}
    		if (preparedStmt2 != null)
    		{
    			try {preparedStmt2.close();} catch (SQLException e) {} 
    			preparedStmt2 = null;
    		}
     		if (preparedStmt2A != null)
    		{
    			try {preparedStmt2A.close();} catch (SQLException e) {} 
    			preparedStmt2A = null;
    		}
    		if (preparedStmt3 != null)
    		{
    			try {preparedStmt3.close();} catch (SQLException e) {} 
    			preparedStmt3 = null;
    		}
    		if (preparedStmt4 != null)
    		{
    			try {preparedStmt4.close();} catch (SQLException e) {} 
    			preparedStmt4 = null;
    		}
			if (conn != null)
			{
				try {conn.close();} catch (SQLException e) {}
				conn = null;
			}
		}
	}
}
